﻿using System;
using System.Data;
using System.Data.SQLite;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Text.RegularExpressions;
using System.Collections.Specialized;
using System.Collections.Generic;
using System.IO;
using System.Xml;
using System.Reflection;

namespace green
{
    public class litedb 
    {
        //private const string jsonResponse = "<?xml version=\"1.0\" encoding=\"utf-8\"?><result><message error=\"{0}\">{1}</message><rowsaffected>{2}</rowsaffected></result>";
        //private const string jsonResponse = "{{error:{0},message:{1},rowsaffected:{2}}}";
        
        private  const string sqlGetRequestQuery = @"select usrSession, usrGroup,usrId, tileId, tileStatus, tileName, tileMime, tileQuery, tileXsl, tileJs, tileResponse, tileAction from slvi_SessionTiles where (usrSession = '{0}') and (tileId = {1})";
        //private  const string sqlGetTileScript = @"select tileJs from slvi_TileScripts where (usrSession = '{0}') and (tileId = {1})";
        private const string sqlGetTileScript = @"select tileJs from slw_Tiles where tileId = {0}";
        private  const string sqlGetImage = @"select imgBody, imgTitle, imgMime from slw_Images where imgId = {0}";
        private  const string sqlGetScript = @"select jsBody from slw_JS where jsId = {0}";
        private const string sqlGetCss = @"select cssBody from slw_Css where cssId = {0}";
        private const string sqlGetPage = @"select pgName, pgBody, pgMime, pgUpdated from slw_Pages where pgId = {0}";
        private const string sqlGetHash = @"select usrPwd from slw_Users where usrLogin = '{0}'";
        private const string sqlSetSession = @"update slw_Users set usrSession = '{0}' where usrLogin = '{1}'";
        private const string sqlNewImage = @"insert into slw_Images (imgSize, imgTitle, imgMime, imgBody) values (@imgSize,@imgTitle,@imgMime,@imgBody)";
        private const string sqlUpdateImage = @"update slw_Images set imgSize=@imgSize, imgTitle=@imgTitle, imgMime=@imgMime, imgBody=@imgBody where imgId = @imgId";

        //internal static readonly string _liteStr = ConfigurationManager.ConnectionStrings["sqliteCon"].ConnectionString;
        //internal SQLiteConnection _con = new SQLiteConnection(_liteStr);

        internal SQLiteConnection _con;
        #region Helpers
        private  void PrepareParams(SQLiteCommand cmd)
        {
            MatchCollection par = Regex.Matches(cmd.CommandText, @"@\w*\b");
            foreach (Match m in par)
            {
                cmd.Parameters.Add(new SQLiteParameter(m.ToString(), null));
            }


        }


        private void AssignParams(SQLiteCommand cmd, NameValueCollection aList, TileInfo Ti)
        {
            PrepareParams(cmd);
            foreach (SQLiteParameter P in cmd.Parameters)
            {
                string key = P.ParameterName.Substring(1).ToLower();
                switch (key)
                {
                    case "id": P.Value = Ti.UsrId; break;
                    case "gr": P.Value = Ti.UsrGroup; break;
                    default: P.Value = aList[key]; break;
                }
                
            }
        }
        private string FormatReader(IDataReader data)
        {
            string mask = "<row>\n";
            int idx = 0;
            string xOut = "";
            DataTable schema = data.GetSchemaTable();
            foreach (DataRow dr in schema.Rows)
            {
                var S = dr["ColumnName"];
                mask += "<" + S + ">{" + idx++.ToString() + "}</" + S + ">\n";
            }
            mask += "</row>\n";
            while (data.Read())
            {
                object[] vals = new object[idx];
                data.GetValues(vals);
                xOut += String.Format(mask, vals);
            }

            return "<DocumentElement>\n" + xOut + "</DocumentElement>\n";
        }

        #endregion

        public litedb(string conStr)
        {
            _con = new SQLiteConnection(conStr);
        }

        public litedb(SQLiteConnection con)
        {
            _con = con;
        }
        internal TileInfo getTileInfo(int did, string lses)
        {
            TileInfo tile = null;
            using (SQLiteCommand _cmd = _con.CreateCommand())
            {
                _cmd.CommandType = CommandType.Text;
                _cmd.CommandText = String.Format(sqlGetRequestQuery, lses, did);
                
                _con.Open();
                SQLiteDataReader Dr = _cmd.ExecuteReader(CommandBehavior.CloseConnection);
                tile = new TileInfo(Dr);
                _con.Close();
            }
            return tile;
        }

        internal string getTileScript(int did, string lses)
        {
            string jS = "";
            using (SQLiteCommand _cmd = _con.CreateCommand())
            {
                _cmd.CommandType = CommandType.Text;
                //_cmd.CommandText = String.Format(sqlGetTileScript, lses, did);
                _cmd.CommandText = String.Format(sqlGetTileScript, did);
                _con.Open();
                jS = _cmd.ExecuteScalar().ToString();
                _con.Close();
            }
            return jS;
        }

        

        internal  XmlDocument QueryXml(TileInfo Ti, NameValueCollection par)
        {
            string qry = Ti.TileQuery;
            string rStr;
            XmlDocument _xd = new XmlDocument();
                using (SQLiteCommand _cmd = _con.CreateCommand())
                {
                    _cmd.CommandType = CommandType.Text;
                    _cmd.CommandText = qry;
                    AssignParams(_cmd, par, Ti);
                    _con.Open();
                    SQLiteDataReader Dr = _cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    rStr = FormatReader(Dr);
                    _xd.LoadXml(rStr);
                 }
            
            
            
            return _xd;
        }
        
        
        
        #region Image Handlers
        internal ImageInfo GetImage(int id)
        {
            ImageInfo iI = null;
            using (SQLiteCommand _cmd = _con.CreateCommand())
            {
                _cmd.CommandType = CommandType.Text;
                _cmd.CommandText = String.Format(sqlGetImage, id);
                try
                {
                    _con.Open();
                    SQLiteDataReader dr = _cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (dr.Read())
                    {
                        iI = new ImageInfo();
                        iI.Body = (byte[])dr["imgBody"];
                        iI.Mime = dr["imgMime"].ToString();
                        iI.Name = dr["imgTitle"].ToString();

                    }
                    _con.Close();
                }
                catch (Exception e)
                {
                    _con.Close();
                    return null;
                    throw (e);


                }
            }

            return iI;
        }

        internal bool insertImage(string title, string mime, int len, byte[] buff)
        {
            using (SQLiteCommand cmd = _con.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sqlNewImage;
                SQLiteParameter par = new SQLiteParameter("@imgTitle", DbType.String);
                par.Value = title;
                cmd.Parameters.Add(par);
                par = new SQLiteParameter("@imgMime", DbType.String);
                par.Value = mime;
                cmd.Parameters.Add(par);
                par = new SQLiteParameter("@imgSize", DbType.Int32);
                par.Value = len;
                cmd.Parameters.Add(par);
                par = new SQLiteParameter("@imgBody", DbType.Binary);
                par.Value = buff;
                cmd.Parameters.Add(par);
                _con.Open();
                SQLiteTransaction tr = _con.BeginTransaction();
                int aff = cmd.ExecuteNonQuery();
                tr.Commit();
                _con.Close();
                return aff == 1;
            }
        }

        internal bool updateImage(int id, string title, string mime, int len, byte[] buff)
        {
            using (SQLiteCommand cmd = _con.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sqlUpdateImage;
                SQLiteParameter par = new SQLiteParameter("@imgTitle", DbType.String);
                par.Value = title;
                cmd.Parameters.Add(par);
                par = new SQLiteParameter("@imgMime", DbType.String);
                par.Value = mime;
                cmd.Parameters.Add(par);
                par = new SQLiteParameter("@imgSize", DbType.Int32);
                par.Value = len;
                cmd.Parameters.Add(par);
                par = new SQLiteParameter("@imgBody", DbType.Binary);
                par.Value = buff;
                cmd.Parameters.Add(par);
                par = new SQLiteParameter("@imgId", DbType.Int32);
                par.Value = id;
                cmd.Parameters.Add(par);
                _con.Open();
                SQLiteTransaction tr = _con.BeginTransaction();
                int aff = cmd.ExecuteNonQuery();
                tr.Commit();
                _con.Close();
                return aff == 1;
            }
        }
        
        #endregion 
       

        internal string GetScript(int did)
        {
            string jS = "";
            using (SQLiteCommand _cmd = _con.CreateCommand())
            {
                _cmd.CommandType = CommandType.Text;
                _cmd.CommandText = String.Format(sqlGetScript, did);
                _con.Open();
                jS = _cmd.ExecuteScalar().ToString();
                _con.Close();
            }
            return jS;
        }

        internal PageInfo GetPage(int did)
        {
            PageInfo pg = null;
            using (SQLiteCommand _cmd = _con.CreateCommand())
            {
                _cmd.CommandType = CommandType.Text;
                _cmd.CommandText = String.Format(sqlGetPage, did);
                _con.Open();
                SQLiteDataReader dr = _cmd.ExecuteReader(CommandBehavior.CloseConnection);
                if (dr.Read())
                {
                    pg = new PageInfo(dr["pgName"].ToString(), dr["pgMime"].ToString(), dr["pgBody"].ToString(), dr["pgUpdated"].ToString());
                }
                _con.Close();
            }
            return pg;
        }

        internal string GetCss(int did)
        {
            string css = "";
            using (SQLiteCommand _cmd = _con.CreateCommand())
            {
                _cmd.CommandType = CommandType.Text;
                _cmd.CommandText = String.Format(sqlGetCss, did);
                _con.Open();
                css = _cmd.ExecuteScalar().ToString();
                _con.Close();
            }
            return css;
        }

        internal string ActionResponse(TileInfo Ti, NameValueCollection par)
        {
            string qry = Ti.TileAction;
            string _json;
            _con.Open();
            using (SQLiteTransaction T = _con.BeginTransaction())
            {
                char[] separator = new char[1]{';'};
                string[] comArr = qry.Split(separator, StringSplitOptions.RemoveEmptyEntries);
                try
                {
                    int rows = 0;
                    using (SQLiteCommand _cmd = _con.CreateCommand())
                    {
                        foreach (string S in comArr)
                        {
                            _cmd.CommandType = CommandType.Text;
                            _cmd.CommandText = S;
                            AssignParams(_cmd, par,Ti);

                            rows += _cmd.ExecuteNonQuery();
                        }
                    }
                    T.Commit();
                    _json = (Ti.TileResponse != "") ? Ti.TileResponse : String.Format(Constants.jsonResponse, 0, "Commited", rows);
                    //if (Ti.TileResponse != null)
                    //{
                    //    _json = Ti.TileResponse;
                    //}
                    //else
                    //{
                    //    _json = String.Format(Constants.jsonResponse, 0, "Commited", rows);
                    //}

                }
                catch (Exception e)
                {
                    T.Rollback();
                    _json=String.Format(Constants.jsonResponse, 1, "Rolled back - " + e.Message, 0).Replace('\r',' ').Replace('\n',' ');

                }
            }
            _con.Close();
            return _json;
        }

        
        internal string getPwdHash(string login)
        {
            string hash = "";
            using (SQLiteCommand _cmd = _con.CreateCommand())
            {
                _cmd.CommandType = CommandType.Text;
                _cmd.CommandText = String.Format(sqlGetHash, login);
                _con.Open();
                hash = _cmd.ExecuteScalar().ToString();
                _con.Close();
            }
            return hash;
        }

        internal bool setUsrSession(string login, string session)
        {
            int res;
            using (SQLiteCommand _cmd = _con.CreateCommand())
            {
                _cmd.CommandType = CommandType.Text;
                _cmd.CommandText = String.Format(sqlSetSession, session, login);
                _con.Open();
                res = _cmd.ExecuteNonQuery();
                _con.Close();
            }
            return res == 1;
        }



        
    }
}
